<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js" lang="en">
<!--<![endif]-->
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Basic Stored Procedures - The .NET Core ORM Cookbook</title>
    <link rel="shortcut icon" href="favicon.ico">
    <link rel="stylesheet" href="css/theme.css" type="text/css" />
    <link rel="stylesheet" href="css/theme_colors.css" type="text/css" />
    <link rel="stylesheet" href="css/styles/vs.css">
    <link rel="stylesheet" href="css/font-awesome.4.5.0.min.css">
</head>
<body role="document">
    <div class="grid-for-nav">
        <nav data-toggle="nav-shift" class="nav-side stickynav">
            <div class="side-nav-search">
                <a href="index.htm"><i class="fa fa-home"></i> The .NET Core ORM Cookbook</a>
                <div role="search">
                    <form id="search-form" class="form" action="Docnet_search.htm" method="get">
                        <input type="text" name="q" placeholder="Search docs" />
                    </form>
                </div>
            </div>
            <div class="menu menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="tocentry"><a href="index.htm">Home</a>
</li>

<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="ORMs.htm">ORMs</a></span>
</li>
<li class="tocentry"><a href="FAQ.htm">FAQ</a>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Advancedscenarios.htm">Advanced scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Sortingscenarios.htm">Sorting scenarios</a></span>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></span></li>
<li class="tocentry current"><a class="current" href="BasicStoredProc.htm">Basic Stored Procedures</a>
<ul class="currentrelative">
<li class="tocentry"><a href="#scenario-prototype">Scenario Prototype</a></li>

<li class="tocentry"><a href="#database-stored-procedures">Database Stored Procedures</a></li>

<li class="tocentry"><a href="#ado.net">ADO.NET</a></li>

<li class="tocentry"><a href="#chain">Chain</a></li>

<li class="tocentry"><a href="#dapper">Dapper</a></li>

<li class="tocentry"><a href="#dbconnector">DbConnector</a></li>

<li class="tocentry"><a href="#entity-framework-6">Entity Framework 6</a></li>

<li class="tocentry"><a href="#entity-framework-core">Entity Framework Core</a></li>

<li class="tocentry"><a href="#linq-to-db">LINQ to DB</a></li>

<li class="tocentry"><a href="#llblgen-pro">LLBLGen Pro</a></li>

<li class="tocentry"><a href="#nhibernate">NHibernate</a></li>

<li class="tocentry"><a href="#repodb">RepoDb</a></li>

<li class="tocentry"><a href="#servicestack">ServiceStack</a></li>



</ul>

</ul>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Auditingandhistoryscenarios.htm">Auditing and history scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></span>
</li>
</ul>
				<div class="toc-footer">
					<span class="text-small">
						<hr/>
						<a href="https://github.com/FransBouma/DocNet" target="_blank">Made with <i class="fa fa-github"></i> DocNet</a>
					</span>
				</div>	
			</div>
            &nbsp;
        </nav>
        <section data-toggle="nav-shift" class="nav-content-wrap">
            <nav class="nav-top" role="navigation" aria-label="top navigation">
                <i data-toggle="nav-top" class="fa fa-bars"></i>
                <a href="index.htm">The .NET Core ORM Cookbook</a>
            </nav>
            <div class="nav-content">
                <div role="navigation" aria-label="breadcrumbs navigation">
                    <div class="breadcrumbs">
<ul><li><a href="index.htm">Home</a></li> / <li><a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></li> / <li><a href="BasicStoredProc.htm">Basic Stored Procedures</a></li></ul>
					
                    </div>
                    <hr />
                </div>
                <div role="main">
                    <div class="section">
<h1 id="basic-stored-procedures">Basic Stored Procedures<a class="headerlink" href="#basic-stored-procedures" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h1>
<p>These scenarios demonstrate how to call basic stored procedures that return a single resultset. This can be interpreted as a value, a row, or a collection of rows. </p>
<p>Future scenarios will cover topics such as:</p>
<ul>
<li>Multiple Resultsets</li>
<li>Output parameter(s)</li>
<li>Return parameter</li>
</ul>
<h2 id="scenario-prototype">Scenario Prototype<a class="headerlink" href="#scenario-prototype" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public interface IBasicStoredProcScenario&lt;TEmployeeClassification, TEmployeeClassificationWithCount&gt;
   where TEmployeeClassification : class, IEmployeeClassification, new()
   where TEmployeeClassificationWithCount : class, IEmployeeClassificationWithCount
{
    /// &lt;summary&gt;
    /// Execute a stored procedure that returns a collection.
    /// &lt;/summary&gt;
    IList&lt;TEmployeeClassificationWithCount&gt; CountEmployeesByClassification();

    /// &lt;summary&gt;
    /// Execute a stored procedure that takes a parameter object and returns a scalar value.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;employeeClassification&quot;&gt;The employee classification.&lt;/param&gt;
    /// &lt;returns&gt;The employee classification key.&lt;/returns&gt;
    int CreateEmployeeClassification(TEmployeeClassification employeeClassification);

    /// &lt;summary&gt;
    /// Execute a stored procedure that returns a collection, omitting the optional parameter.
    /// &lt;/summary&gt;
    IList&lt;TEmployeeClassification&gt; GetEmployeeClassifications();

    /// &lt;summary&gt;
    /// Execute a stored procedure that returns a collection, providing an optional parameter.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;employeesClassificationKey&quot;&gt;The employee classification key.&lt;/param&gt;
    TEmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey);
}
</code></pre>

<h2 id="database-stored-procedures">Database Stored Procedures<a class="headerlink" href="#database-stored-procedures" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre class="nocode">CREATE PROCEDURE HR.CountEmployeesByClassification
AS
BEGIN
    SET NOCOUNT ON;
    SELECT COUNT(e.EmployeeKey) AS EmployeeCount,
           ec.EmployeeClassificationKey,
           ec.EmployeeClassificationName
    FROM HR.EmployeeClassification ec
        LEFT JOIN HR.Employee e
            ON e.EmployeeClassificationKey = ec.EmployeeClassificationKey
    GROUP BY ec.EmployeeClassificationKey,
             ec.EmployeeClassificationName
    ORDER BY ec.EmployeeClassificationName;

    RETURN 0;
END;
</pre><pre class="nocode">CREATE PROCEDURE HR.CreateEmployeeClassification
    @EmployeeClassificationName VARCHAR(30),
    @IsExempt BIT,
    @IsEmployee BIT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO HR.EmployeeClassification
    (
        EmployeeClassificationName,
        IsExempt,
        IsEmployee
    )
    VALUES
    (@EmployeeClassificationName, @IsExempt, @IsEmployee);

    --Use a temp variable to ensure the correct data type
    DECLARE @EmployeeClassificationKey INT = SCOPE_IDENTITY();
    SELECT @EmployeeClassificationKey AS EmployeeClassificationKey;

    RETURN 0;
END;
</pre><pre class="nocode">CREATE PROCEDURE HR.GetEmployeeClassifications @EmployeeClassificationKey INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ec.EmployeeClassificationKey,
           ec.EmployeeClassificationName,
           ec.IsExempt,
           ec.IsEmployee
    FROM HR.EmployeeClassification ec
    WHERE (@EmployeeClassificationKey IS NULL)
          OR (ec.EmployeeClassificationKey = @EmployeeClassificationKey);

    RETURN 0;
END;
</pre><h2 id="ado.net">ADO.NET<a class="headerlink" href="#ado.net" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class BasicStoredProcScenario : SqlServerScenarioBase,
    IBasicStoredProcScenario&lt;EmployeeClassification, EmployeeClassificationWithCount&gt;
{
    public BasicStoredProcScenario(string connectionString) : base(connectionString)
    { }

    public IList&lt;EmployeeClassificationWithCount&gt; CountEmployeesByClassification()
    {
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(&quot;HR.CountEmployeesByClassification&quot;, con)
        { CommandType = CommandType.StoredProcedure })
        using (var reader = cmd.ExecuteReader())
        {
            var results = new List&lt;EmployeeClassificationWithCount&gt;();
            while (reader.Read())
                results.Add(new EmployeeClassificationWithCount(reader));

            return results;
        }
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $&quot;{nameof(employeeClassification)} is null.&quot;);

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(&quot;HR.CreateEmployeeClassification&quot;, con)
        { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationName&quot;,
                    employeeClassification.EmployeeClassificationName);
            cmd.Parameters.AddWithValue(&quot;@IsEmployee&quot;, employeeClassification.IsEmployee);
            cmd.Parameters.AddWithValue(&quot;@IsExempt&quot;, employeeClassification.IsExempt);

            return (int)cmd.ExecuteScalar();
        }
    }

    public IList&lt;EmployeeClassification&gt; GetEmployeeClassifications()
    {
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(&quot;HR.GetEmployeeClassifications&quot;, con)
        { CommandType = CommandType.StoredProcedure })
        using (var reader = cmd.ExecuteReader())
        {
            var results = new List&lt;EmployeeClassification&gt;();
            while (reader.Read())
                results.Add(new EmployeeClassification(reader));

            return results;
        }
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(&quot;HR.GetEmployeeClassifications&quot;, con)
        { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue(&quot;@EmployeeClassificationKey&quot;, employeeClassificationKey);
            using (var reader = cmd.ExecuteReader())
            {
                var results = new List&lt;EmployeeClassification&gt;();
                if (reader.Read())
                    return new EmployeeClassification(reader);
                else
                    return null;
            }
        }
    }
}
</code></pre>

<h2 id="chain">Chain<a class="headerlink" href="#chain" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class BasicStoredProcScenario :
    IBasicStoredProcScenario&lt;EmployeeClassification, EmployeeClassificationWithCount&gt;
{
    readonly SqlServerDataSource m_DataSource;

    public BasicStoredProcScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public IList&lt;EmployeeClassificationWithCount&gt; CountEmployeesByClassification()
    {
        return m_DataSource.Procedure(&quot;HR.CountEmployeesByClassification&quot;)
            .ToCollection&lt;EmployeeClassificationWithCount&gt;().Execute();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $&quot;{nameof(employeeClassification)} is null.&quot;);

        //Chain will ask the database which parameters it needs from the object. Other properties on the object will
        //be ignored, so there's no need to manually map it to an anonymous type.

        return m_DataSource.Procedure(&quot;HR.CreateEmployeeClassification&quot;, employeeClassification).ToInt32().Execute();
    }

    public IList&lt;EmployeeClassification&gt; GetEmployeeClassifications()
    {
        return m_DataSource.Procedure(&quot;HR.GetEmployeeClassifications&quot;)
            .ToCollection&lt;EmployeeClassification&gt;().Execute();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        return m_DataSource.Procedure(&quot;HR.GetEmployeeClassifications&quot;, new { employeeClassificationKey })
            .ToObjectOrNull&lt;EmployeeClassification&gt;().Execute();
    }
}
</code></pre>

<h2 id="dapper">Dapper<a class="headerlink" href="#dapper" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class BasicStoredProcScenario : ScenarioBase,
    IBasicStoredProcScenario&lt;EmployeeClassification, EmployeeClassificationWithCount&gt;
{
    public BasicStoredProcScenario(string connectionString) : base(connectionString)
    { }

    public IList&lt;EmployeeClassificationWithCount&gt; CountEmployeesByClassification()
    {
        using (var con = OpenConnection())
            return con.Query&lt;EmployeeClassificationWithCount&gt;(&quot;HR.CountEmployeesByClassification&quot;,
                commandType: CommandType.StoredProcedure).ToList();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $&quot;{nameof(employeeClassification)} is null.&quot;);

        //Need to copy the parameters so we can exclude the unused EmployeeClassificationKey
        using (var con = OpenConnection())
            return con.ExecuteScalar&lt;int&gt;(&quot;HR.CreateEmployeeClassification&quot;,
                new
                {
                    employeeClassification.EmployeeClassificationName,
                    employeeClassification.IsEmployee,
                    employeeClassification.IsExempt
                }, commandType: CommandType.StoredProcedure);
    }

    public IList&lt;EmployeeClassification&gt; GetEmployeeClassifications()
    {
        using (var con = OpenConnection())
            return con.Query&lt;EmployeeClassification&gt;(&quot;HR.GetEmployeeClassifications&quot;,
                commandType: CommandType.StoredProcedure).ToList();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var con = OpenConnection())
            return con.Query&lt;EmployeeClassification&gt;(&quot;HR.GetEmployeeClassifications&quot;,
                new { employeeClassificationKey }, commandType: CommandType.StoredProcedure).SingleOrDefault();
    }
}
</code></pre>

<h2 id="dbconnector">DbConnector<a class="headerlink" href="#dbconnector" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class BasicStoredProcScenario : ScenarioBase,
    IBasicStoredProcScenario&lt;EmployeeClassification, EmployeeClassificationWithCount&gt;
{
    public BasicStoredProcScenario(string connectionString) : base(connectionString)
    { }

    public IList&lt;EmployeeClassificationWithCount&gt; CountEmployeesByClassification()
    {
        return DbConnector.ReadToList&lt;EmployeeClassificationWithCount&gt;(&quot;HR.CountEmployeesByClassification&quot;,
            commandType: CommandType.StoredProcedure).Execute();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $&quot;{nameof(employeeClassification)} is null.&quot;);

        //Need to project the parameters so we can exclude the unused EmployeeClassificationKey
        return DbConnector.Scalar&lt;int&gt;(&quot;HR.CreateEmployeeClassification&quot;,
            new
            {
                employeeClassification.EmployeeClassificationName,
                employeeClassification.IsEmployee,
                employeeClassification.IsExempt
            }, commandType: CommandType.StoredProcedure).Execute();
    }

    public IList&lt;EmployeeClassification&gt; GetEmployeeClassifications()
    {
        return DbConnector.ReadToList&lt;EmployeeClassification&gt;(&quot;HR.GetEmployeeClassifications&quot;,
            commandType: CommandType.StoredProcedure).Execute();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        return DbConnector.ReadSingleOrDefault&lt;EmployeeClassification&gt;(&quot;HR.GetEmployeeClassifications&quot;,
            new { employeeClassificationKey }, commandType: CommandType.StoredProcedure).Execute();
    }
}
</code></pre>

<h2 id="entity-framework-6">Entity Framework 6<a class="headerlink" href="#entity-framework-6" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="entity-framework-core">Entity Framework Core<a class="headerlink" href="#entity-framework-core" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>To use stored procedures that return values, a class is needed to receive the results. This is true even if a scalar value is returned.</p>
<pre><code class="cs">public class EmployeeClassificationWithCount : IEmployeeClassificationWithCount
{
    public int EmployeeClassificationKey { get; set; }
    public string? EmployeeClassificationName { get; set; }
    public int EmployeeCount { get; set; }
}
</code></pre>

<pre><code class="cs">public class EmployeeClassificationKeyHolder
{
    public int EmployeeClassificationKey { get; set; }
}
</code></pre>

<p>The receiver class should be registered in the DbContext as shown below:</p>
<pre><code class="cs">partial class OrmCookbookContext : DbContext
{
    //Using &quot;= null!;&quot; to remove the compiler warning.
    //Assume that the DbContext constructor will populate these properties
    public virtual DbSet&lt;EmployeeClassificationKeyHolder&gt; EmployeeClassificationKeyHolder { get; set; } = null!;

    public virtual DbSet&lt;EmployeeClassificationWithCount&gt; EmployeeClassificationWithCount { get; set; } = null!;

    static void RegisterEntitiesForStoredProcedures(ModelBuilder modelBuilder)
    {
        //Output types for stored procedures. Not generated by Scaffold-DbContext

        modelBuilder.Entity&lt;EmployeeClassificationWithCount&gt;(entity =&gt;
        {
            entity.HasNoKey();

            entity.Property(e =&gt; e.EmployeeClassificationName).IsUnicode(false);
        });

        modelBuilder.Entity&lt;EmployeeClassificationKeyHolder&gt;(entity =&gt;
        {
            entity.HasNoKey();
        });
    }
}
</code></pre>

<pre><code class="cs">public class BasicStoredProcScenario :
    IBasicStoredProcScenario&lt;EmployeeClassification, EmployeeClassificationWithCount&gt;
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public BasicStoredProcScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public IList&lt;EmployeeClassificationWithCount&gt; CountEmployeesByClassification()
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassificationWithCount
                .FromSqlRaw(&quot;EXEC HR.CountEmployeesByClassification;&quot;).ToList();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $&quot;{nameof(employeeClassification)} is null.&quot;);

        //Notes:
        //EF Core cannot return scalar values from stored procedures. A holder class is needed to receive the
        //results.
        //Named parameters are not supported, so parameter order is important.
        using (var context = CreateDbContext())
        {
            var temp = context.EmployeeClassificationKeyHolder
                  .FromSqlRaw(&quot;EXEC HR.CreateEmployeeClassification {0}, {1}, {2};&quot;,
                      employeeClassification.EmployeeClassificationName,
                      employeeClassification.IsExempt,
                      employeeClassification.IsEmployee
                  ).ToList();

            //Single isn't allowed for stored procedures. Thus ToList must be called first.
            return temp.Single().EmployeeClassificationKey;
        }
    }

    public IList&lt;EmployeeClassification&gt; GetEmployeeClassifications()
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassifications.FromSqlRaw(&quot;EXEC HR.GetEmployeeClassifications;&quot;).ToList();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            var temp = context.EmployeeClassifications.FromSqlRaw(&quot;EXEC HR.GetEmployeeClassifications {0};&quot;,
                employeeClassificationKey).ToList();
            //Note that SingleOrDefault isn't allowed for stored procedures. Thus ToList must be called first.
            return temp.SingleOrDefault();
        }
    }
}
</code></pre>

<h2 id="linq-to-db">LINQ to DB<a class="headerlink" href="#linq-to-db" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>To use stored procedures that return values, a class is needed to receive the results. This is true even if a scalar value is returned.</p>
<pre><code class="cs">public class BasicStoredProcScenario : IBasicStoredProcScenario&lt;EmployeeClassification, EmployeeClassificationWithCount&gt;
{
    public IList&lt;EmployeeClassificationWithCount&gt; CountEmployeesByClassification()
    {
        using (var db = new OrmCookbook())
            return db.QueryProc&lt;EmployeeClassificationWithCount&gt;(&quot;HR.CountEmployeesByClassification&quot;).ToList();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification), $&quot;{nameof(employeeClassification)} is null.&quot;);

        //Notes:
        //LINQ to DB cannot return scalar values from stored procedures. A holder class is needed to receive the results.
        using (var db = new OrmCookbook())
            return db.QueryProc&lt;EmployeeClassificationKeyHolder&gt;(&quot;HR.CreateEmployeeClassification&quot;,
                  new DataParameter(&quot;@EmployeeClassificationName&quot;, employeeClassification.EmployeeClassificationName),
                  new DataParameter(&quot;@IsExempt&quot;, employeeClassification.IsExempt),
                  new DataParameter(&quot;@IsEmployee&quot;, employeeClassification.IsEmployee)
                ).Single().EmployeeClassificationKey;
    }

    public IList&lt;EmployeeClassification&gt; GetEmployeeClassifications()
    {
        using (var db = new OrmCookbook())
            return db.QueryProc&lt;EmployeeClassification&gt;(&quot;HR.GetEmployeeClassifications&quot;).ToList();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
        {
            return db.QueryProc&lt;EmployeeClassification&gt;(&quot;HR.GetEmployeeClassifications&quot;,
                new DataParameter(&quot;@EmployeeClassificationKey&quot;, employeeClassificationKey)
                ).SingleOrDefault();
        }
    }
}
</code></pre>

<h2 id="llblgen-pro">LLBLGen Pro<a class="headerlink" href="#llblgen-pro" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>To use stored procedures that return a resultset, a class is needed to receive the results.</p>
<pre><code class="cs">public class BasicStoredProcScenario : IBasicStoredProcScenario&lt;GetEmployeeClassificationsResultRow, CountEmployeesByClassificationResultRow&gt;
{
    public IList&lt;CountEmployeesByClassificationResultRow&gt; CountEmployeesByClassification()
    {
        // Use the generated calls to the procedure and pass in a generated projection to a poco to project the resultset
        // to a set of poco instances. The Fetch... method creates a new adapter instance. We could also pass one in.
        return RetrievalProcedures.FetchCountEmployeesByClassificationResultTypedView(
                                        new QueryFactory().GetCountEmployeesByClassificationResultTypedViewProjection());
    }


    public int CreateEmployeeClassification(GetEmployeeClassificationsResultRow employeeClassification)
    {
        if(employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification), $&quot;{nameof(employeeClassification)} is null.&quot;);

        // The stored procedure in question returns the created PK value as a resultset and not as an output parameter. 
        // We therefore have to call the procedure as if it's returning a resultset. 
        var result = RetrievalProcedures.FetchCreateEmployeeClassificationResultTypedView(
                                                  new QueryFactory().GetCreateEmployeeClassificationResultTypedViewProjection(),
                                                  employeeClassification.EmployeeClassificationName, employeeClassification.IsExempt,
                                                  employeeClassification.IsEmployee)
                                        .FirstOrDefault();
        return result?.EmployeeClassificationKey ?? 0;
    }


    public IList&lt;GetEmployeeClassificationsResultRow&gt; GetEmployeeClassifications()
    {
        return RetrievalProcedures.FetchGetEmployeeClassificationsResultTypedView(
                                                new QueryFactory().GetGetEmployeeClassificationsResultTypedViewProjection(), 
                                                null);
    }


    public GetEmployeeClassificationsResultRow? GetEmployeeClassifications(int employeeClassificationKey)
    {
        return RetrievalProcedures.FetchGetEmployeeClassificationsResultTypedView(
                                                new QueryFactory().GetGetEmployeeClassificationsResultTypedViewProjection(), 
                                                employeeClassificationKey).FirstOrDefault();
    }
}
</code></pre>

<h2 id="nhibernate">NHibernate<a class="headerlink" href="#nhibernate" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="repodb">RepoDb<a class="headerlink" href="#repodb" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class BasicStoredProcScenario : DbRepository&lt;SqlConnection&gt;,
    IBasicStoredProcScenario&lt;EmployeeClassification, EmployeeClassificationWithCount&gt;
{
    public BasicStoredProcScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public IList&lt;EmployeeClassificationWithCount&gt; CountEmployeesByClassification()
    {
        return ExecuteQuery&lt;EmployeeClassificationWithCount&gt;(&quot;[HR].[CountEmployeesByClassification]&quot;,
            commandType: CommandType.StoredProcedure).AsList();
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification),
                $&quot;{nameof(employeeClassification)} is null.&quot;);

        return ExecuteScalar&lt;int&gt;(&quot;[HR].[CreateEmployeeClassification]&quot;, new
        {
            employeeClassification.EmployeeClassificationName,
            employeeClassification.IsExempt,
            employeeClassification.IsEmployee
        }, commandType: CommandType.StoredProcedure);
    }

    public IList&lt;EmployeeClassification&gt; GetEmployeeClassifications()
    {
        return ExecuteQuery&lt;EmployeeClassification&gt;(&quot;[HR].[GetEmployeeClassifications]&quot;,
            commandType: CommandType.StoredProcedure).AsList();
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        return ExecuteQuery&lt;EmployeeClassification&gt;(&quot;[HR].[GetEmployeeClassifications]&quot;,
            new { EmployeeClassificationKey = employeeClassificationKey },
            commandType: CommandType.StoredProcedure).FirstOrDefault();
    }
}
</code></pre>

<h2 id="servicestack">ServiceStack<a class="headerlink" href="#servicestack" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class BasicStoredProcScenario : IBasicStoredProcScenario&lt;EmployeeClassification, EmployeeClassificationWithCount&gt;
{
    private readonly IDbConnectionFactory _dbConnectionFactory;

    public BasicStoredProcScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public IList&lt;EmployeeClassificationWithCount&gt; CountEmployeesByClassification()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using (var cmd = db.SqlProc(&quot;HR.CountEmployeesByClassification&quot;))
            {
                return cmd.ConvertToList&lt;EmployeeClassificationWithCount&gt;();
            }
        }
    }

    public int CreateEmployeeClassification(EmployeeClassification employeeClassification)
    {
        if (employeeClassification == null)
            throw new ArgumentNullException(nameof(employeeClassification), $&quot;{nameof(employeeClassification)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using (var cmd = db.SqlProc(&quot;HR.CreateEmployeeClassification&quot;, new { employeeClassification.EmployeeClassificationName, employeeClassification.IsEmployee, employeeClassification.IsExempt }))
            {
                return (int) cmd.Scalar();
            }
        }
    }

    public IList&lt;EmployeeClassification&gt; GetEmployeeClassifications()
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using (var cmd = db.SqlProc(&quot;HR.GetEmployeeClassifications&quot;))
            {
                return cmd.ConvertToList&lt;EmployeeClassification&gt;();
            }
        }
    }

    public EmployeeClassification? GetEmployeeClassifications(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using (var cmd = db.SqlProc(&quot;HR.GetEmployeeClassifications&quot;, new { EmployeeClassificationKey = employeeClassificationKey }))
            {
                return cmd.ConvertTo&lt;EmployeeClassification&gt;();
            }
        }
    }
}
</code></pre>


                    </div>
                </div>
                <footer>
                    <hr />
                    <div role="contentinfo">
The ORM Cookbook. <a href='https://github.com/Grauenwolf/DotNet-ORM-Cookbook' target='_blank'>Visit us at GitHub</a>.
                    </div>
                </footer>
            </div>
        </section>
    </div>
    <script src="js/jquery-2.1.1.min.js"></script>
    <script src="js/modernizr-2.8.3.min.js"></script>
    <script src="js/highlight.pack.js"></script>
    <script src="js/theme.js"></script>

</body>
</html>
